package com.itcast.demo.excelToJson.utils;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.*;

public class ExcelToJsonConverter {

    private static final SimpleDateFormat DATE_FORMAT = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

    public static String convert(MultipartFile file) throws IOException {
        return convert(file.getInputStream());
    }

    public static String convert(InputStream inputStream) throws IOException {
        List<Map<String, Object>> result = new ArrayList<>();

        try (Workbook workbook = WorkbookFactory.create(inputStream)) {
            Sheet sheet = workbook.getSheetAt(0);
            Iterator<Row> rowIterator = sheet.iterator();

            // 获取标题行
            Row headerRow = rowIterator.next();
            List<String> headers = new ArrayList<>();
            for (Cell cell : headerRow) {
                headers.add(cell.getStringCellValue());
            }

            // 处理数据行
            while (rowIterator.hasNext()) {
                Row row = rowIterator.next();
                Map<String, Object> rowData = new LinkedHashMap<>();

                for (int i = 0; i < headers.size(); i++) {
                    Cell cell = row.getCell(i, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                    String header = headers.get(i);
                    rowData.put(header, getCellValue(cell));
                }

                result.add(rowData);
            }
        }

        return new com.fasterxml.jackson.databind.ObjectMapper().writeValueAsString(result);
    }

    private static Object getCellValue(Cell cell) {
        CellType cellType = cell.getCellType();
        
        switch (cellType) {
            case STRING:
                return cell.getStringCellValue().trim();
            
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return DATE_FORMAT.format(cell.getDateCellValue());
                }
                double numericValue = cell.getNumericCellValue();
                if (numericValue == Math.floor(numericValue)) {
                    long intValue = (long) numericValue;
                    return (intValue <= Integer.MAX_VALUE && intValue >= Integer.MIN_VALUE) 
                           ? (int) intValue : intValue;
                }
                return numericValue;
            
            case BOOLEAN:
                return cell.getBooleanCellValue();
            
            case FORMULA:
                return evaluateFormulaCell(cell);
            
            case BLANK:
            default:
                return null;
        }
    }

    private static Object evaluateFormulaCell(Cell cell) {
        FormulaEvaluator evaluator = cell.getSheet().getWorkbook()
                .getCreationHelper().createFormulaEvaluator();
        CellValue cellValue = evaluator.evaluate(cell);
        
        switch (cellValue.getCellType()) {
            case STRING: return cellValue.getStringValue();
            case NUMERIC: return cellValue.getNumberValue();
            case BOOLEAN: return cellValue.getBooleanValue();
            default: return null;
        }
    }
}